﻿using Newtonsoft.Json;
using Spire.Xls;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace winXml
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //文件类型过滤
            openFileDialog1.Filter = "(*.xml)|*.xml";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                textBox1.Text = openFileDialog1.FileName;
            }
        }

        private DataTable ToDataTable(DataRow[] rows)
        {
            if (rows == null || rows.Length == 0) return null;
            DataTable tmp = rows[0].Table.Clone(); // 复制DataRow的表结构
            foreach (DataRow row in rows)
            {

                tmp.ImportRow(row); // 将DataRow添加到DataTable中
            }
            return tmp;
        }

        private void DelectDir(string srcPath)
        {
            try
            {
                DirectoryInfo dir = new DirectoryInfo(srcPath);
                FileSystemInfo[] fileinfo = dir.GetFileSystemInfos();  //返回目录中所有文件和子目录
                foreach (FileSystemInfo i in fileinfo)
                {
                    if (i is DirectoryInfo)            //判断是否文件夹
                    {
                        DirectoryInfo subdir = new DirectoryInfo(i.FullName);
                        subdir.Delete(true);          //删除子目录和文件
                    }
                    else
                    {
                        File.Delete(i.FullName);      //删除指定文件
                    }
                }
            }
            catch (Exception e)
            {
                throw;
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //得到文件路径全名
            var sourcePath = openFileDialog1.FileName;
            var invoicePath = openFileDialog2.FileName;
            if (string.IsNullOrEmpty(sourcePath) || string.IsNullOrEmpty(invoicePath))
            {
                MessageBox.Show("请选择源文件和开票信息！");
                return;
            }
            var tmpPath = Directory.GetCurrentDirectory() + "\\tmp.xlsx";
            var tarPath = Directory.GetCurrentDirectory() + "\\tmp";
            var date = DateTime.Now.ToString("yyyy.MM.dd");
            if (false == Directory.Exists(tarPath))
            {
                //创建文件夹
                Directory.CreateDirectory(tarPath);
            }
            else
            {
                DelectDir(tarPath);
                Directory.CreateDirectory(tarPath);
            }
            Workbook workbook1 = new Workbook();
            workbook1.LoadFromXml(sourcePath);
            //Initailize worksheet
            workbook1.SaveToFile(tmpPath, ExcelVersion.Version2010);
            Workbook sourceWorkbook = new Workbook();
            sourceWorkbook.LoadFromFile(tmpPath);
            Worksheet sourceSheet = sourceWorkbook.Worksheets[0];
            Workbook invoiceWorkbook = new Workbook();
            invoiceWorkbook.LoadFromFile(invoicePath);
            Worksheet invoiceSheet = invoiceWorkbook.Worksheets[0];
            var sourceData = new DataTable();
            var invoiceData = new DataTable();
            try
            {
                sourceData = sourceSheet.ExportDataTable();
                invoiceData = invoiceSheet.ExportDataTable();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
            List<SourceInfo> sourceInfos = new List<SourceInfo>();
            List<InvoiceInfo> invoiceInfos = new List<InvoiceInfo>();
            foreach (DataRow item in sourceData.Rows)
            {
                SourceInfo sourceInfo = new SourceInfo()
                {
                    Amount = item["金额(保险价格)"].ToString(),
                    BankName = item["银行名称"].ToString(),
                    BankNumber = item["银行账号"].ToString(),
                    ContractId = item["合同号"].ToString(),
                    InsurancePremium = item["保险费"].ToString(),
                    InvoiceName = item["开票方名称"].ToString(),
                    OrderQuantity = item["订单数量"].ToString(),
                    Province = item["地区名字-省"].ToString(),
                    Rate = item["费率"].ToString(),
                    RegistrationNumber = "'" + item["增值税登记号"].ToString(),
                    ShippingType = item["运输方式"].ToString(),
                    Street = item["住宅号及街道"].ToString(),
                    Total = item["总件数"].ToString(),
                    TransportUnit = item["运输单位"].ToString(),
                    Type = item["类型"].ToString(),
                    Type2 = item["类型2"].ToString()
                };
                sourceInfos.Add(sourceInfo);
            }
            foreach (DataRow item in invoiceData.Rows)
            {
                InvoiceInfo invoiceInfo = new InvoiceInfo()
                {
                    Id = item["合同号"].ToString(),
                    DemanderTelephone = "'" + item["需方电话"].ToString(),
                    DemanderNumber = "'" + item["需方税号"].ToString(),
                    DemanderAddress = item["需方地址"].ToString(),
                    DemanderBank = "'" + item["需方开户银行"].ToString(),
                    DemanderBankNumber = "'" + item["需方银行账号"].ToString()
                };
                invoiceInfos.Add(invoiceInfo);
            }
            var strJson = JsonConvert.SerializeObject(sourceInfos);
            var data = JsonConvert.DeserializeObject<DataTable>(strJson);
            //DataTable data = ExcelHepler.ListToDataTable<object>(list);


            var han = sourceInfos.Where(x => x.Type == "含").ToList();
            var bao = sourceInfos.Where(x => x.Type == "包").ToList();
            var baoputong = sourceInfos.Where(x => x.Type == "包" && string.IsNullOrEmpty(x.BankNumber)).ToList();
            var baozhuanyong = sourceInfos.Where(x => x.Type == "包" && !string.IsNullOrEmpty(x.BankNumber) && !string.IsNullOrWhiteSpace(x.BankName)).ToList();

            if (han.Count > 0)
            {
                var data1 = JsonConvert.DeserializeObject<DataTable>(JsonConvert.SerializeObject(GetInfoTxt(han)));
                data1.Columns.Remove("序号");
                data1.Columns.Remove("电话");
                ExcelHepler.DataTableToExcel(data1, tarPath + "\\" + date + "含.xls");
            }
            if (bao.Count > 0)
            {
                var data2 = JsonConvert.DeserializeObject<DataTable>(JsonConvert.SerializeObject(GetInfoTxt(bao)));
                data2.Columns.Remove("序号");
                data2.Columns.Remove("电话");
                ExcelHepler.DataTableToExcel(data2, tarPath + "\\" + date + "包.xls");
            }

            if (baoputong.Count > 0)
            {
                string sourceExt = JsonConvert.SerializeObject(GetInfoExt(GetInfo(baoputong, invoiceInfos), "普通"));
                var data3 = JsonConvert.DeserializeObject<DataTable>(sourceExt);
                data3.Columns.Remove("运输单位");
                data3.Columns.Remove("订单数量");
                data3.Columns.Remove("运输方式");
                ExcelHepler.DataTableToExcel(data3, tarPath + "\\" + date + "包普通.xls");
            }

            if (baozhuanyong.Count > 0)
            {
                string sourceExt2 = JsonConvert.SerializeObject(GetInfoExt(GetInfo(baozhuanyong, invoiceInfos), "专用"));
                var data4 = JsonConvert.DeserializeObject<DataTable>(sourceExt2);
                data4.Columns.Remove("运输单位");
                data4.Columns.Remove("订单数量");
                data4.Columns.Remove("运输方式");
                ExcelHepler.DataTableToExcel(data4, tarPath + "\\" + date + "包专用.xls");
            }
            MessageBox.Show("导出成功！");
            System.Diagnostics.Process.Start("explorer.exe", tarPath);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //文件类型过滤
            openFileDialog2.Filter = "(*.xls;*xlsx)|*.xls;*xlsx";
            if (openFileDialog2.ShowDialog() == DialogResult.OK)
            {
                textBox2.Text = openFileDialog2.FileName;
            }
        }

        private List<SourceInfo> GetInfo(List<SourceInfo> sourceInfos, List<InvoiceInfo> invoiceInfos)
        {
            List<SourceInfo> result = new List<SourceInfo>();
            sourceInfos = sourceInfos.Where(x => !string.IsNullOrEmpty(x.InvoiceName)).OrderBy(x => x.InvoiceName).ToList();
            string invoiceName = "";
            int i = 0;
            var sumData = sourceInfos.GroupBy(x => x.InvoiceName, (k, g) => new
            {
                InvoiceName = k,
                AmountSum = g.Sum(s => string.IsNullOrEmpty(s.Amount) ? 0 : decimal.Parse(s.Amount)),
                InsurancePremiumSum = g.Sum(s => string.IsNullOrEmpty(s.InsurancePremium) ? 0 : decimal.Parse(s.InsurancePremium)),
                TotalSum = g.Sum(s => string.IsNullOrEmpty(s.Total) ? 0 : decimal.Parse(s.Total)),
            }).ToList();
            sourceInfos.ForEach(x =>
            {
                x.Id = 1;
                var item = invoiceInfos.FirstOrDefault(m => m.Id == x.ContractId);
                if (item != null)
                {
                    x.BankName = item?.DemanderBank;
                    x.Street = item?.DemanderAddress;
                    x.RegistrationNumber = item?.DemanderNumber;
                    x.Telephone = item?.DemanderTelephone;
                    x.BankNumber = item?.DemanderBankNumber;
                }
                if (invoiceName.TrimStart().TrimEnd() != x.InvoiceName.TrimStart().TrimEnd())
                {
                    x.Id = i + 1;
                    var sum = sumData.FirstOrDefault(p => p.InvoiceName == invoiceName);
                    SourceInfo sourceInfo = new SourceInfo()
                    {
                        Amount = sum?.AmountSum.ToString("0.00"),
                        InsurancePremium = sum?.InsurancePremiumSum.ToString("0.00"),
                        Total = sum?.TotalSum.ToString("0.00"),
                    };
                    invoiceName = x.InvoiceName;
                    if (i != 0)
                    {
                        result.Add(sourceInfo);
                    }
                    i++;
                }
                else
                {
                    x.Id = null;
                }
                result.Add(x);
                if (x == sourceInfos.LastOrDefault())
                {
                    var sum = sumData.FirstOrDefault(p => p.InvoiceName == invoiceName);
                    SourceInfo sourceInfo = new SourceInfo()
                    {
                        Amount = sum?.AmountSum.ToString("0.00"),
                        InsurancePremium = sum?.InsurancePremiumSum.ToString("0.00"),
                        Total = sum?.TotalSum.ToString("0.00"),
                    };
                    result.Add(sourceInfo);
                }
            });
            return result;
        }

        public List<SourceInfoExt> GetInfoExt(List<SourceInfo> sourceInfos, string typeStr)
        {
            List<SourceInfoExt> list = new List<SourceInfoExt>();

            foreach (var item in sourceInfos)
            {
                SourceInfoExt info = new SourceInfoExt();
                info.Amount = item.Amount;
                info.BankName = item.BankName;
                info.BankNumber = item.BankNumber;
                info.ContractId = item.ContractId;
                info.InsurancePremium = item.InsurancePremium;
                info.InvoiceName = item.InvoiceName;
                info.OrderQuantity = item.OrderQuantity;
                info.Province = item.Province;
                info.Rate = item.Rate;
                info.RegistrationNumber = item.RegistrationNumber;
                info.ShippingType = item.ShippingType;
                info.Street = item.Street;
                info.Telephone = item.Telephone;
                info.Total = item.Total;
                info.TransportUnit = item.TransportUnit;
                info.Type = item.Type;
                info.Type2 = item.Type2;
                if (item.Id.HasValue)
                {
                    info.Id = item.Id.ToString();
                }
                list.Add(info);
            }
            return list;
        }

        public List<SourceInfo> GetInfoTxt(List<SourceInfo> sourceInfos)
        {
            List<SourceInfo> list = new List<SourceInfo>();
            foreach (var item in sourceInfos)
            {
                SourceInfo info = new SourceInfo();
                info.Amount = item.Amount;
                info.BankName = item.BankName;
                info.BankNumber = "'" + item.BankNumber;
                info.ContractId = item.ContractId;
                info.InsurancePremium = item.InsurancePremium;
                info.InvoiceName = item.InvoiceName;
                info.OrderQuantity = item.OrderQuantity;
                info.Province = item.Province;
                info.Rate = item.Rate;
                info.RegistrationNumber = item.RegistrationNumber;
                info.ShippingType = item.ShippingType;
                info.Street = item.Street;
                info.Telephone = item.Telephone;
                info.Total = item.Total;
                info.TransportUnit = item.TransportUnit;
                info.Type = item.Type;
                info.Type2 = item.Type2;
                if (item.Id.HasValue)
                {
                    info.Id = item.Id.Value;
                }
                list.Add(info);
            }
            return list;
        }
    }
}
